package com.dy.dao;

import com.dy.baan.Sugar;
import com.dy.baan.SugarPp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT b2.`name`,sum(o.final_total_amount)'VALUE' FROM order_info o \n" +
            "LEFT JOIN order_detail d on o.id = d.order_id\n" +
            "LEFT JOIN sku_info s on d.sku_id = s.id\n" +
            "LEFT JOIN base_category3 b3 on s.category3_id =b3.id\n" +
            "LEFT JOIN base_category2 b2 on b3.category2_id = b2.id\n" +
            "GROUP BY b2.`name`")
    List<Sugar> showTwo();
    @Select("SELECT CONCAT(SUBSTRING(user_info.birthday,3,1),'0后')`name`,sum(order_info.final_total_amount)'VALUE' FROM user_info , order_info WHERE user_info.id =order_info.user_id \n" +
            "GROUP BY CONCAT(SUBSTRING(user_info.birthday,3,1),'0后')")
    List<Sugar> showAge();
    @Select("SELECT yys'name',sum(final_total_amount)'value' FROM order_info GROUP BY yys HAVING yys is not null;")
    List<Sugar> showYys();
    @Select("SELECT b.`name`,IF(u.gender = 'F','男','女')gender,sum(o.final_total_amount)'value' FROM order_info o \n" +
            "LEFT JOIN user_info u on o.user_id = u.id\n" +
            "LEFT JOIN base_province b on o.province_id = b.id\n" +
            "GROUP BY b.`name`,gender")
    List<SugarPp> showPp();


}
